═══ 1. About RexxGDB2 ═══ RexxGDB2.ZIP and the files in it are FREEWARE. RexxGDB2.ZIP contains RexxGDB2.DLL, (this) online document, license agreement, some test- and benchmark Rexx programs and many more files. RexxGDB2.DLL is a library of Rexx functions for IBM OS/2 (Warp) which allow Rexx programs to manipulate data in IBM Database 2 for OS/2 (DB2/2) database tables through SQL statements. Each of these Rexx functions performs one or several SQL-related tasks, and none works as a common all-purpose Rexx DB2/2 API for all SQL statements like IBM's SQLEXEC (which is included with each copy of IBM DB2/2). The design allows the Rexx functions to use static SQL (when they were built) and to be fine-tuned separately which make them overall faster compared to SQLEXEC. Author: Simon Husin of Kent, Washington, U.S.A. E-mail: husin@ibm.net FAX: 1-206-813-8202 ═══ 2. Words of thanks ═══ This package would not be possible without the involvement of the following parties, directly and indirectly: o Mom and Dad in Antwerp, Belgium (for life and education) o Mike Cowlishaw, IBM Fellow, IBM UK Laboratories (for his Rexx, Goserve and PMPrintF) o Numerous people within IBM (for its OS/2, Warp, and Database for OS/2) Lastly, my love and thanks to Liza and Jacqueline, my wife and daughter at home, who give me so much love, inspiration, motivations, and time... ═══ 3. Contents ═══ RexxGDB2.ZIP Contains the following files ReadMe.1st - Release Highlights, Tips, and Last-minute notes License.TXT - License information BindGDB2.CMD - To bind RexxGDB2.BND file to your database(s) RexxGDB2.BND - Static SQL statements to be bound w/ BINDGDB2.CMD RexxGDB2.DLL - Library with the Rexx DB2/2 functions RexxGDB2.INF - Online document file (you are viewing at) RexxGDB2.TXT - Sample text to upload/post RexxGDB2.ZIP anywhere G2Funcs.CMD - Program to test all RexxGDB2 functions G2Immed.CMD - G2Immediate (and G2Rollback) testing program G2Cols.CMD - G2SelectCols testing program G2Data.CMD - G2SelectData testing program G2Form.CMD - G2SelectForm testing program G2one.CMD - G2SelectOne testing program G2Free.CMD - To rollback last transactions, disconnect from currently connected database, and drop (unload) all RexxGDB2 functions DropGDB2.CMD - To drop (unload) all RexxGDB2 functions Run-Exec - Benchmark program using SQLEXEC API Run-G2 - Benchmark program using SQLEXEC & RexxGDB2 APIs TestLong.CMD - To test the long SQL and returned value support by G2SelectCols, G2SelectData, G2SelectForm, and G2SelectOne TestMult.CMD - To test RexxGDB2 multi-threaded support Thread1...8 - Programs called by TestMult GToknSet.CMD - To create table G2Table in Sample database used by GTOKNTST GToknTst.CMD - To test RexxGDB2 G2GetToken function G2Blob.CMD - G2SelectBlob testing program G2Clob.CMD - G2SelectClob testing program G2Ha.CMD - To test RexxGDB2 G2HSelect2Textarea function G2Hc.CMD - To test RexxGDB2 G2HSelect2Clob function G2Hi.CMD - To test RexxGDB2 G2HSelect2Img function G2Hl.CMD - To test RexxGDB2 G2HSelect2List function G2Hlb.CMD - To test RexxGDB2 G2HSelect2Listbox function G2Hn.CMD - To test RexxGDB2 G2HSelect2Input function G2Ht.CMD - To test RexxGDB2 G2HSelect2Table function G2Hx.CMD - To test RexxGDB2 G2HSelect2H function G2HTable.80 - Sample Goserve filter that interfaces with RexxGDB2 G2HTable.HTM - Sample First WWW Page served by Goserve via G2HTable.80 StartUP.CMD - Sample script to iniate a single-user local IP server Server.CMD - Sample script to launch WWW Server (Goserve) Browser.CMD - Sample script to launch WWW Browser (Netscape Navigator) ═══ 4. System Requirements ═══ Hardware IBM PC or compatible computers running with Intel 386SX 16Mhz, or any compatible and faster processors, with 2 Mbytes of RAM above the OS/2 and DB2/2 requirements. In addition, the RexxGDB2 package also requires 310 kbytes of disk space for its files. Software Rexx functions included in the RexxGDB2.DLL are all 32-bit which require IBM Operating System/2 (OS/2) version 2.x or newer with the Rexx-engine installed, and IBM Database 2 for OS/2 (DB2/2) version 2.1 or newer. ═══ 5. Release Notes ═══ =========================================================================== Version 1.30 Released on Sunday, August 10th, 1997. --------------------------------------------------------------------------- New functions are introduced to select large objects into files. HTML-generating functions are introduced. They are all functions which names start with G2H. RexxGDB2 now also supports Mike Cowlishaw's WWW Server, GoServe. All functions are better stream-lined to use less resources. Multi-threaded support is safer and more robust. =========================================================================== Version 1.20 Released on Sunday, November 17th, 1996. --------------------------------------------------------------------------- The Rexx functions now are now optimized for 486-class processors. They will still run on 386- and Pentium or 586-class or higher processors. G2GetToken is introduced to ease the creation of Integer or Smallint tokens (surrogate keys). =========================================================================== Version 1.10 Released on Sunday, August 11th, 1996. --------------------------------------------------------------------------- The Rexx functions now runs properly in a multi-threaded Rexx application environment. G2SelectCols, G2SelectData, G2SelectForm, and G2SelectOne can now receive long SQL statements and return long character strings. Please read the reference for each of the above-mentioned functions. G2SelectOne now introduces a new (Rexx) variable called G2LongResult. Please read the G2SelectOne section for more about it. =========================================================================== Version 1.00 Released on Sunday, December 10th, 1995. --------------------------------------------------------------------------- The Rexx functions have been tested, since the development started in April of 1995, first on the single and server versions of IBM DB2/2 version 1.2., and later continued (and modified) to ONLY support IBM DB2/2 version 2.x. The Rexx functions are compatible with Quercus Personal/Rexx version 3.0 for OS/2. Personal/Rexx version 3.0 for OS/2 is a compatible replacement for the IBM OS/2 Rexx engine (PL 2/Rexx), has extended functions, and performs better than PL 2/Rexx, especially when performing loops. ═══ 6. How to use RexxGDB2 ═══ There are two steps and areas required to be prepared for RexxGDB2: o Creating packages on your DB2/2 databases o Registering and loading the Rexx functions in your OS/2 session Creating packages on your DB2/2 databases Before using RexxGDB2 Rexx functions to manipulate existing DB2/2 databases, you must (once) create the RexxGDB2 package (contained in RexxGDB2.BND) on each of those DB2/2 database(s) using BindGDB2.CMD. To have DB2/2 automatically create a RexxGDB2 package for new or migrated DB2/2 databases, you must include RexxGDB2.BND in the list of 'bind files' contained in DB2UBIND.LST. BindGDB2.CMD can be run from the OS/2 command prompt from the subdirectory where both BindGDB2.CMD and RexxGDB2.BND reside. When prompted, just type the name of your existing DB2/2 database and press Enter. You can repeat the process to bind to multiple databases, or just press Enter to quit. DB2UBIND.LST (residing in the DB2/2 subdirectory, \SQLLIB\BND) can be changed using any text editor to add the RexxGDB2.BND to the list. If your database has been created, this method does not automatically create the package on your database! In this case, you must either run BindGDB2.CMD or execute the Bind statement using the DB2 command line processor to bind the DB2UBIND.LST, e.g. 'db2 connect to sample in share mode' followed by 'db2 bind @DB2UBIND.LST'. NOTES: How the RexxGDB2.BND is bound influences the behaviour of its functions. Without changes, BindGDB2.CMD will use the default settings which are the following, among other things: o Isolation Level supported is 'Cursor Stability' o Date and timestamp format is ISO o Query optimization level 5 If you want to customize RexxGDB2 to better suit your specific environment, issue those specific options when (re-) binding RexxGDB2.BND. Example: On an OS/2 command prompt, issue the following statements: DB2 CONNECT TO SAMPLE IN SHARE MODE DB2 BIND RexxGDB2.BND ISOLATION UR DATETIME USA QUERYOPT 0 Registering and loading the Rexx functions in your OS/2 session First, make sure RexxGDB2.DLL resides in a subdirectory that is included in the LIBPATH (OS/2 system variable). You can either copy RexxGDB2.DLL into a subdirectory that has been included there, such as [your-drive:]\OS2\DLL, or change your CONFIG.SYS to include RexxGDB2 subdirectory there and reboot the system before using it. Second, in your Rexx application program (or Startup.CMD), you must load RexxGDB2 functions using the standard OS/2 Rexx function RXFUNCADD. To load all functions, you can first load the function G2LoadFuncs, and call it to load all functions in RexxGDB2 DLL (see the example below). Unless dropped, the functions need not be loaded again until the next system boot up. Example: if RXFuncQuery('G2LoadFuncs') then do call RXFuncAdd 'G2LoadFuncs', 'RexxGDB2', 'G2LoadFuncs' call G2LoadFuncs end How to run the sample programs included with this package Log on as USERID Example: [Warp prompt C:] LOGON /L USERID /P:SECRET Activate the DB2/2 engine Example: [Warp prompt C:] DB2START Before continuing... Now ensure that... 1. DB2/2 Sample has been created (using DB2SAMPL) 2. RexxGDB2 has been installed as described above At this stage you can run all sample programs which names do not start with G2H: 3a.Examples: Run-G2.CMD Run-Exec.CMD Testlong.CMD You need to install Netscape Navigator 2.xx (or newer) for OS/2 to be able to run HTML-generating test programs as is. The names of these test (Rexx) programs start with G2H. As an alternative, you could modify these programs to use IBM WWW Explorer (Explore) instead of Netscape. 3b.Examples: G2Hi.CMD G2Ht.CMD G2Hlb.CMD ═══ 7. How to use RexxGDB2 with Goserve ═══ Items discussed here: o What is Goserve o What is PMPrintF o How Goserve and RexxGDB2 work together o How to set up and use Goserve and RexxGDB2 sample resources What is Goserve Goserve is a Worldwide Web server for OS/2 written by Mike Cowlishaw, IBM Fellow, IBM UK Laboratories. Goserve is available on numerous sites such as anonymous FTPs, WWWs, and BBSes. To go to the source, check Mike Cowlishaw's WWW site: http://www2.hursley.ibm.com What is PMPrintF PMPrintF is another useful program written by Mike Cowlishaw, IBM Fellow, IBM UK Laboratories It works well with Goserve and is useful to debug problems that may occur while you are testing in building and providing WWW pages using RexxGDB2 functions. PMPrintF is also available on numerous sites such as anonymous FTPs, WWWs, and BBSes. To go to the source, check Mike Cowlishaw's WWW site: http://www2.hursley.ibm.com How Goserve and RexxGDB2 work together Goserve is the link between what the clients see and the DB2/2 database data. It ensures that the initial and subsequent WWW pages are available when requested. When a request is received by Goserve to retrieve data from DB2/2, it connects to the appropriate DB2/2 database using the userid already available on the system where Goserve runs. One or more Goserve (Rexx) scripts build a flat file containing the necessary HTMLs using RexxGDB2 functions running in OS/2 address space (ADDRESS CMD) invoked within the Goserve (Rexx) scripts. When done, Goserve sends the file back to the client as a WWW page. Summary: o Goserve receives requests for DB2/2 data o Goserve connects to the DB2/2 o Goserve builds the WWW pages o Goserve disconnects from the DB2/2 o Goserve sends the DB2/2 data on WWW pages to the requestor Note: Although Goserve connects and disconnects per each request, we notice that the first connection remains active for the entire Goserve session. This explains why the first DB2/2 data request handled by Goserve takes more time compared to the same request processed later. How to set up and use Goserve and RexxGDB2 sample resources Log on as USERID Example: [Warp prompt C:] LOGON /L USERID /P:SECRET Activate the DB2/2 engine Example: [Warp prompt C:] DB2START Before continuing... Now ensure that... 1. DB2/2 Sample has been created (using DB2SAMPL) 2. RexxGDB2 has been installed as described above 3. Netscape Navigator 2.xx (or newer) for OS/2 has been properly installed 4. The OS/2 Warp TCP/IP has been set up correctly 5. The TCP/IP loopback driver is installed See the StartUp.CMD in this package and Goserve.DOC for details 6. Your Goserve resources are correctly installed on C:\Goserve 7. You have copied the following resources into C:\Goserve a. G2HTable.80 b. Server.CMD c. Browser.CMD d. G2HTable.HTM 8. You have configured Goserve to use G2HTable.80 filter The following may help you configure your Goserve for that... a. Type Goserve HTTP and press Enter from within Goserve subdirectory b. Click on Goserve Options c. Tab to Filter d. Enter G2HTable.80 e Click on Apply f. Close the Options notebook g. Stop Goserve Start Server (Goserve) Example: [Warp prompt C:] Server.CMD Start Browser (Netscape Navigator) Example: [Warp prompt C:] Browser.CMD Enter an SQL statement on the WWW page Example: select * from staff Click on Process ═══ 8. Available External Functions ═══ The external Rexx functions contained in RexxGDB2.DLL are (sorted alphabetically): ┌────────────────────┬────────────────────────────────────────┐ │Name │Description │ ├────────────────────┼────────────────────────────────────────┤ │G2Commit │Issue DB2/2 SQL Commit to make changes │ │ │permanent │ ├────────────────────┼────────────────────────────────────────┤ │G2ConnectExclusive │Connect to a DB2/2 database in exclusive│ │ │mode │ ├────────────────────┼────────────────────────────────────────┤ │G2ConnectReset │Disconnect a previously connected DB2/2 │ ├────────────────────┼────────────────────────────────────────┤ │G2ConnectShare │Connect to a DB2/2 database in share │ │ │mode │ ├────────────────────┼────────────────────────────────────────┤ │G2CurDate │Get and return CURRENT (server system) │ │ │DATE (as RESULT) │ ├────────────────────┼────────────────────────────────────────┤ │G2CurExplSnap │Get and return CURRENT EXPLAIN SNAPSHOT │ │ │(as RESULT) │ ├────────────────────┼────────────────────────────────────────┤ │G2CurFuncPath │Get and return CURRENT FUNCTION PATH (as│ │ │RESULT) │ ├────────────────────┼────────────────────────────────────────┤ │G2CurQueryOpt │Get and return CURRENT QUERY │ │ │OPTIMIZATION (as RESULT) │ ├────────────────────┼────────────────────────────────────────┤ │G2CurServer │Get and return CURRENT SERVER name (as │ │ │RESULT) │ ├────────────────────┼────────────────────────────────────────┤ │G2CurTime │Get and return CURRENT (server system) │ │ │TIME (as RESULT) │ ├────────────────────┼────────────────────────────────────────┤ │G2CurTimeStamp │Get and return CURRENT (server system) │ │ │TIMESTAMP (as RESULT) │ ├────────────────────┼────────────────────────────────────────┤ │G2CurTimeZone │Get and return CURRENT (server system) │ │ │TIMEZONE (as RESULT) │ ├────────────────────┼────────────────────────────────────────┤ │G2DropFuncs │Unload (drop) all functions from │ │ │internal memory │ ├────────────────────┼────────────────────────────────────────┤ │G2GetToken │Execute a DB2/2 SQL to obtain and │ │ │increase a surrogate key │ ├────────────────────┼────────────────────────────────────────┤ │G2H │Write a string to a flat file, with or │ │ │without HTML (-paired) tag(s). │ ├────────────────────┼────────────────────────────────────────┤ │G2HSelect2Clob │Execute a DB2/2 SQL Select statement to │ │ │get a CLOB and write it to a flat file │ │ │as HTML textarea element. │ ├────────────────────┼────────────────────────────────────────┤ │G2HSelect2Img │Execute a DB2/2 SQL Select statement to │ │ │get a BLOB and write it to a flat file │ │ │as HTML image element. │ ├────────────────────┼────────────────────────────────────────┤ │G2HSelect2Input │Execute a DB2/2 SQL Select statement to │ │ │get rows and columns of fields and write│ │ │them to a flat file as HTML input │ │ │elements. │ ├────────────────────┼────────────────────────────────────────┤ │G2HSelect2List │Execute a DB2/2 SQL Select statement to │ │ │get rows of data and write them to a │ │ │flat file as HTML list elements. │ ├────────────────────┼────────────────────────────────────────┤ │G2HSelect2Listbox │Execute a DB2/2 SQL Select statement to │ │ │get rows and columns of fields and write│ │ │them to a flat file as HTML select and │ │ │option elements. │ ├────────────────────┼────────────────────────────────────────┤ │G2HSelect2Table │Execute a DB2/2 SQL Select statement to │ │ │get rows and columns of fields and write│ │ │them to a flat file as HTML table │ │ │elements. │ ├────────────────────┼────────────────────────────────────────┤ │G2HSelect2Textarea │Execute a DB2/2 SQL Select statement to │ │ │get rows and columns of fields and write│ │ │them to a flat file as an HTML textarea │ │ │element. │ ├────────────────────┼────────────────────────────────────────┤ │G2Immediate │Execute a DB2/2 SQL Execute Immediate │ │ │for some SQL statements │ ├────────────────────┼────────────────────────────────────────┤ │G2LoadFuncs │Load all functions in the library into │ │ │internal memory │ ├────────────────────┼────────────────────────────────────────┤ │G2Rollback │Issue DB2/2 SQL Rollback to cancel all │ │ │uncommitted changes │ ├────────────────────┼────────────────────────────────────────┤ │G2SelectBlob │Execute a DB2/2 SQL Select statement to │ │ │get a binary large object into a file. │ ├────────────────────┼────────────────────────────────────────┤ │G2SelectClob │Execute a DB2/2 SQL Select statement to │ │ │get a text/character large object into a│ │ │file. │ ├────────────────────┼────────────────────────────────────────┤ │G2SelectCols │Execute a DB2/2 SQL Select statement to │ │ │get selected column names, datatype, and│ │ │length in a 1-dimensional stem. │ ├────────────────────┼────────────────────────────────────────┤ │G2SelectData │Execute a DB2/2 SQL Select statement to │ │ │get one or specified number of rows of │ │ │raw data in a 2-dimensional stem. │ ├────────────────────┼────────────────────────────────────────┤ │G2SelectForm │Execute a DB2/2 SQL Select statement to │ │ │get one or specified number of rows of │ │ │formatted data and one heading line in a│ │ │1-dimensional stem. │ ├────────────────────┼────────────────────────────────────────┤ │G2SelectOne │Execute a DB2/2 SQL Select statement to │ │ │return one row-column data (as RESULT) │ ├────────────────────┼────────────────────────────────────────┤ │G2SetConnection │Set database connection to current (from│ │ │dormant) │ ├────────────────────┼────────────────────────────────────────┤ │G2SetCurExplSnap │Set CURRENT EXPLAIN SNAPSHOT (to NO, │ │ │YES, or EXPLAIN) │ ├────────────────────┼────────────────────────────────────────┤ │G2SetCurPkgSet │Set CURRENT PACKAGESET (to use another │ │ │collection) │ ├────────────────────┼────────────────────────────────────────┤ │G2SetCurQueryOpt │Set CURRENT QUERY OPTIMIZATION (to 0, 1,│ │ │3, 5, or 9) │ ├────────────────────┼────────────────────────────────────────┤ │G2User │Get and return the (connected) USER id. │ │ │(as RESULT) │ └────────────────────┴────────────────────────────────────────┘ ═══ 9. Load/Unload ═══ Loading and unloading functions. ═══ 9.1. G2LoadFuncs * Load all functions ═══ This function loads all RexxGDB2 functions into (internal) memory. Syntax: result = G2LoadFuncs() Params: None (parameters will be ignored) Result: 1 when successful 0 when unsucessful ═══ 9.2. G2DropFuncs * Unload all functions ═══ This function unloads (removes) all RexxGDB2 functions from memory. Syntax: result = G2DropFuncs() Params: None (parameters will be ignored) Result: 1 when successful 0 when unsucessful ═══ 10. Connect/Disconnect ═══ Connecting and disconnection functions. ═══ 10.1. G2ConnectExclusive * Connect to a database in exclusive mode ═══ This function connects your application to the specified database in exclusive mode. Syntax: result = G2ConnectExclusive(database) /* 1 */ Params: 1. database (required) Name of the DB2/2 Database to be connected in exclusive mode Result: DB2/2 SQLCODE ═══ 10.2. G2ConnectShare * Connect to a database in share mode ═══ This function connects your application to the specified database in share mode. Syntax: result = G2ConnectShare(database) /* 1 */ Params: 1. database (required) Name of the DB2/2 Database to be connected in share mode Result: DB2/2 SQLCODE ═══ 10.3. G2ConnectReset * Disconnect from the currently connected database ═══ This function disconnects your application from the currently connected database. Syntax: result = G2ConnectReset() Params: None (parameters will be ignored) Result: DB2/2 SQLCODE ═══ 11. Commit/Rollback ═══ Committing or cancelling changes made so far. ═══ 11.1. G2Commit * Make changes permanent ═══ This function makes all changes, made from the last commit or rollback, permanent. Syntax: result = G2Commit() Params: None (parameters will be ignored) Result: DB2/2 SQLCODE ═══ 11.2. G2Rollback * Cancel all changes ═══ This function cancels all changes, made from the last commit or rollback. Syntax: result = G2Rollback() Params: None (parameters will be ignored) Result: DB2/2 SQLCODE ═══ 12. Execute Immediate ═══ Executing SQL statements other than SELECT. ═══ 12.1. G2Immediate * Execute any one SQL statement, other than SELECT ═══ This function executes any one SQL statement other than SELECT. Syntax: result = G2Immediate(non-select-sql) /* 1 */ Params: 1. non-select-sql (required) Any of the following SQL statements: ALTER COMMENT ON COMMIT CREATE DELETE DROP GRANT INSERT (single & simple insert, i.e. without cursor) LOCK TABLE REVOKE ROLLBACK SET CONSTRAINTS SET CURRENT EXPLAIN SNAPSHOT SET CURRENT FUNCTION PATH SET CURRENT QUERY OPTIMIZATION SET EVENT MONITOR STATE SIGNAL SQLSTATE UPDATE (searched update, i.e. without cursor) Result: DB2/2 SQLCODE ═══ 13. Retrieve DB2/2 data with SELECT ═══ Retrieving one or more information about DB2/2 table columns, and/or their rows and columns with various functions through SQL SELECT statement. ═══ 13.1. G2SelectBlob * Retrieve selected BLOB into a file ═══ This function processes an SQL statement, and get the selected BLOB into a file. Syntax: result = G2SelectBlob(SQL_select_statement, /* 1 */ Blobfn) /* 2 */ Params: 1. SQL_select_statement (required, functioning as input) All tokens for a standard SQL Select statement, selecting only one table column with datatype = BLOB. Example: SELECT picture FROM emp_photo where... 2. Blobfn - File name to contain the BLOB retrieved from the database (required, holding the retrieved BLOB) Result: DB2/2 SQLCODE ═══ 13.2. G2SelectClob * Retrieve selected CLOB into a file ═══ This function processes an SQL statement, and get the selected CLOB into a file. Syntax: result = G2SelectClob(SQL_select_statement, /* 1 */ Clobfn) /* 2 */ Params: 1. SQL_select_statement (required, functioning as input) All tokens for a standard SQL Select statement, selecting only one table column with datatype = CLOB. Example: SELECT resume FROM emp_resume where... 2. Clobfn - File name to contain the CLOB retrieved from the database (required, holding the retrieved CLOB) Result: DB2/2 SQLCODE ═══ 13.3. G2SelectCols * Retrieve selected columns information ═══ This function populates a specified "stem." with selected columns name, datatype, and length. Syntax: result = G2SelectCols(SQL-Select-statement, /* 1 */ Rexx-stem) /* 2 */ Params: 1. SQL-Select-statement (required) All tokens for a standard SQL Select statement Examples: a. 'SELECT * FROM STAFF' b. 'SELECT COUNT(*) FROM ORG' c. Assuming MYSQL contains 'SELECT * FROM STAFF' Use MYSQL (without the quotes since you are passing the value of MYSQL to G2SelectCols) d. 'MYSQL' (include quotes since you are telling G2SelectCols to use the SQL Select Statement contained in the passed Rexx variable directly) e. 'MYSQLSTEM.INDEX' (include quotes since you are telling G2SelectCols to use the SQL Select Statement contained in the passed Rexx variable directly) 2. Rexx-stem - Stem variable name to place results in (required) stem.0 = total number of columns found by DB2/2 stem.x = column-name column-type column-length x = a sequence number starting from 1 (one) to the number contained in stem.0 column-name = DB2/2 table column name or sequence number as returned by DB2/2 column-type = any valid DB2/2 SQLTYPE As described in the DB2/2 Programming Reference Data Structures for SQLDA column-length=the column data length as returned by the DB2/2 For decimal column-types (484 or 485), it returns the length before the decimal period, the decimal period itself (.), and the length following the decimal period. Example: DECIMAL(7,2) will be returned as 5.2 Result: DB2/2 SQLCODE ═══ 13.4. G2SelectData * Retrieve selected rows and columns data ═══ This function populates a specified "stem." with selected rows and columns of data. The data is presented in a two-dimensional "stem.", one identifies the row position, while the other the column position. Syntax: result = G2SelectData(SQL-Select-statement, /* 1 */ Rexx-stem, /* 2 */ requested-rows, /* 3 */ nullchar) /* 4 */ Params: 1. SQL-Select-statement (required) All tokens for a standard SQL Select statement -or- Name of the Rexx variable/stem element containing the SQL statement Examples: a. 'SELECT * FROM STAFF' b. 'SELECT COUNT(*) FROM ORG' c. Assuming MYSQL contains 'SELECT * FROM STAFF' Use MYSQL (without the quotes since you are passing the value of MYSQL to G2SelectData) d. 'MYSQL' (include quotes since you are telling G2SelectData to use the SQL Select Statement contained in the passed Rexx variable directly) e. 'MYSQLSTEM.INDEX' (include quotes since you are telling G2SelectData to use the SQL Select Statement contained in the passed Rexx variable directly) 2. Rexx-stem - Stem variable name to place results in (required) stem.0 = total number of rows returned stem.0.0 = total number of columns returned stem.r.c = row-column data r = row sequence number, starting from 1 (one) to the number contained in stem.0 c = column sequence number, starting from 1 to the number contained in stem.0.0 row-column data = any data retrieved by DB2/2 nul-ended string formatted left justified negative sign (-), if exists, will be placed in the first position 3. Requested-rows - number of rows requested to be returned (optional) If not supplied or if incorrect, it is defaulted to 1 If less rows found than what here is specified, all found rows will still be returned. In any case the number returned in stem.0 should be used to know how many rows are returned. stem.0 value will never exceed the number supplied within this argument. 4. Null character - character used to represent a 'NULL' (optional) If not supplied or if incorrect, it is defaulted to '' If the parameter is longer than one character, only the first character will be used. Result: DB2/2 SQLCODE ═══ 13.5. G2SelectForm * Retrieve and format selected rows of data ═══ This function populates a specified "stem." with selected and formatted rows of data. Syntax: result = G2SelectForm(SQL-Select-statement, /* 1 */ Rexx-stem, /* 2 */ requested-rows, /* 3 */ nullchar, /* 4 */ pad-char, /* 5 */ varcharlen) /* 6 */ Params: 1. SQL-Select-statement (required) All tokens for a standard SQL Select statement Examples: a. 'SELECT * FROM STAFF' b. 'SELECT COUNT(*) FROM ORG' c. Assuming MYSQL contains 'SELECT * FROM STAFF' Use MYSQL (without the quotes since you are passing the value of MYSQL to G2SelectForm) d. 'MYSQL' (include the quotes since you are telling G2SelectForm to use the SQL Select Statement contained in the passed Rexx variable directly) e. 'MYSQLSTEM.INDEX' (include the quotes since you are telling G2SelectForm to use the SQL Select Statement contained in the passed Rexx variable directly) 2. Rexx-stem - Stem variable name to place results in (required) stem.0 = total number of rows returned stem.0.0 = header with column name (or sequence number) stem.r = row data r = row sequence number, start from 1 (one) to the number contained in stem.0 row data = any data retrieved by DB2/2 nul-ended string formatted left justified 3. requested-rows - number of rows requested to be returned (optional, default = 1) If not supplied or if incorrect, it is defaulted to 1 If less rows found than what here is specified, all found rows will still be returned. In any case the number returned in stem.0 should be used to know how many rows are returned. stem.0 value will never exceed the number supplied within this argument. 4. nullchar - character used to represent a 'NULL' or any data of unknown datatype (optional, default = '') If not supplied or if incorrect, it is defaulted to '' If the parameter is longer than one character, only the first character will be used. The entire column width will be filled by this character. 5. pad-char - character to be used as filler (optional, default = CHAR(250)) If not supplied it is defaulted to CHAR(250), i.e. (·) It is used to fill column heading which is not covering the entire width. 6. varcharlen - maximum length of DB2/2 VARCHAR column data (optional, default = 50) If not supplied it is defaulted to 50 (fifty chars.). Result: DB2/2 SQLCODE ═══ 13.6. G2SelectOne * Return one and first selected row and column data ═══ Process an SQL statement (parm.# 1), get the first row and column and return it (as RESULT) to the caller. Rexx variable G2SQLcode will be populated with the DB2/2 SQLCODE. Rexx variable G2LongResult will be populated either with NULL ('') or a string longer than 254 characters which cannot be returned by the function (due to IBM OS/2 Rexx limitations). In this case, the function only returns the first 254 (left most) characters. Syntax: result = G2SelectOne(SQL-Select-statement, /* 1 */ nullchar) /* 2 */ Params: 1. SQL-Select-statement (required) All tokens for a standard SQL Select statement Examples: a. 'SELECT * FROM STAFF' b. 'SELECT COUNT(*) FROM ORG' c. Assuming MYSQL contains 'SELECT * FROM STAFF' Use MYSQL (without the quotes since you are passing the value of MYSQL to G2SelectOne) d. 'MYSQL' (include the quotes since you are telling G2SelectOne to use the SQL Select Statement contained in the passed Rexx variable directly) e. 'MYSQLSTEM.INDEX' (include the quotes since you are telling G2SelectOne to use the SQL Select Statement contained in the passed Rexx variable directly) 2. nullchar - character used to represent a 'NULL' (optional) If not supplied or if incorrect, it is defaulted to '' If the parameter is longer than one character, only the the first character will be used. Result: '' (null string) - IF ... - a row - column was found but not populated (NULL) - no row was found - error ---OR--- String (alphanumeric) - IF... the select was successful ---OR--- Supplied null character (parameter #2) - IF... the parameter was specified and the selected data was not populated (NULL) Other: G2SQLCODE contains the DB2/2 SQLCODE G2LONGRESULT contains either Rexx NULL character ('') or a string longer than 254 characters ═══ 14. DB2/2 Special Registers ═══ The functions contained herein are to retrieve and/or set DB2/2 2.x special registers. ═══ 14.1. Date & Time Registers ═══ The functions contained herein are to retrieve DB2/2 server current clock date and/or time ═══ 14.1.1. G2CurDate * Retrieve current date ═══ Retrieve the current clock date from the DB2/2 server. Rexx variable G2SQLcode will be populated with the DB2/2 SQLCODE. Syntax: result = G2CurDate() Params: None (parameters will be ignored) Result: Current date in either one of the following format, depending on the datetime format used during binding or creation of the package. Datetime code Format returned ISO yyyy-mm-dd USA mm/dd/yyyy EUR dd.mm.yyyy JIS yyyy-mm-dd LOC Depends on database country code Null ('') if errors were detected, e.g. when no DB2/2 connection exists. Other: G2SQLCODE contains the DB2/2 SQLCODE ═══ 14.1.2. G2CurTime * Retrieve current time ═══ Retrieve the current clock time from the DB2/2 server. Rexx variable G2SQLcode will be populated with the DB2/2 SQLCODE. Syntax: result = G2CurTime() Params: None (parameters will be ignored) Result: Current time in either one of the following format, depending on the datetime format used during binding or creation of the package. Datetime code Format returned ISO hh.mm.ss USA hh:mm AM or hh:mm PM EUR hh.mm.ss JIS hh:mm:ss LOC Depends on database country code Null ('') if errors were detected, e.g. when no DB2/2 connection exists. Other: G2SQLCODE contains the DB2/2 SQLCODE ═══ 14.1.3. G2CurTimeStamp * Retrieve current date and timestamp ═══ Retrieve the current clock date and timestamp from the DB2/2 server. Rexx variable G2SQLcode will be populated with the DB2/2 SQLCODE. Syntax: result = G2CurTimeStamp() Params: None (parameters will be ignored) Result: Current date and timestamp in IBM format, i.e. yyyy-mm-dd-hh.mm.ss.nnnnnn Null ('') if errors were detected, e.g. when no DB2/2 connection exists. Other: G2SQLCODE contains the DB2/2 SQLCODE ═══ 14.1.4. G2CurTimeZone * Retrieve time difference with UTC time ═══ Retrieve time difference between the DB2/2 server and the UTC time (formerly known as GMT). Rexx variable G2SQLcode will be populated with the DB2/2 SQLCODE. Syntax: result = G2CurTimeZone() Params: None (parameters will be ignored) Result: Time difference in 6 digits, i.e. [+ or -] hhmmss. UTC time = CURRENT TIMESTAMP - CURRENT TIMEZONE Null ('') if errors were detected, e.g. when no DB2/2 connection exists. Other: G2SQLCODE contains the DB2/2 SQLCODE ═══ 14.2. Explain Snapshot ═══ The functions contained herein are to retrieve and set DB2/2 explain snapshot facility. ═══ 14.2.1. G2CurExplSnap * Retrieve current Explain snapshot register ═══ Retrieve the value of the DB2/2 register which controls the explain snapshot facility. Syntax: result = G2CurExplSnap() Params: None (parameters will be ignored) Result: YES, NO, or EXPLAIN Null ('') if errors were detected, e.g. when no DB2/2 connection exists. Other: G2SQLCODE contains the DB2/2 SQLCODE ═══ 14.2.2. G2SetCurExplSnap * Set current Explain Snapshot register ═══ Change and set the value of the DB2/2 register to control the explain snapshot facility. Syntax: result = G2SetCurExplSnap(explparm) /* 1 */ Params: 1. explparm (required) Any character supported by SET CURRENT EXPLAIN SNAPSHOT. At the moment, only three parameters are available, i.e. NO, YES, or EXPLAIN. Result: DB2/2 SQLCODE ═══ 14.3. Query Optimization ═══ The functions contained herein are to retrieve and set DB2/2 query optimization class. ═══ 14.3.1. G2CurQueryOpt * Retrieve current Query Optimization register ═══ Retrieve the value of the DB2/2 register which controls the query optimization class used to bind (PREPARE) dynamic SQLs. Syntax: result = G2CurQueryOpt() Params: None (parameters will be ignored) Result: Query Optimization class 0, 1, 3, 5, or 9 Where: 0 = minimal optimization, fastest to bind 1 = optimization class comparable to the one used in DB2/2 prior to DB2/2 2.x 3 = optimization class closely resembling the one used in DB2 (for MVS/ESA) 5 = default class which instructs significant optimization designed to produce excellent access path for mixed queries 9 = maximal optimization, slowest to bind Null ('') if errors were detected, e.g. when no DB2/2 connection exists. Other: G2SQLCODE contains the DB2/2 SQLCODE ═══ 14.3.2. G2SetCurQueryOpt * Set current Query Optimization register ═══ Change and set the value of the DB2/2 register to control the explain snapshot facility. Syntax: result = G2SetCurExplSnap(opt_class) /* 1 */ Params: 1. opt_class (required) Any class supported by SET CURRENT QUERY OPTIMIZATION, which is currently: 0 = minimal optimization, fastest to bind 1 = optimization class comparable to the one used in DB2/2 prior to DB2/2 2.x 3 = optimization class closely resembling the one used in DB2 (for MVS/ESA) 5 = default class which instructs significant optimization designed to produce excellent access path for mixed queries 9 = maximal optimization, slowest to bind Result: DB2/2 SQLCODE ═══ 14.4. Other registers ═══ The functions contained herein are to obtain values of the remaining DB2/2 special registers. ═══ 14.4.1. G2CurFuncPath * Retrieve current Function Path register ═══ Retrieve the value of the DB2/2 register which controls dynamically prepared function and datatype references included in the SQL statements. Syntax: result = G2CurFuncPath() Params: None (parameters will be ignored) Result: The current function path which may look as follows: "SYSIBM","SYSFUNC","SIMON" Null ('') if errors were detected, e.g. when no DB2/2 connection exists. Other: G2SQLCODE contains the DB2/2 SQLCODE ═══ 14.4.2. G2CurServer * Retrieve current Server register ═══ Retrieve the value of the DB2/2 register which holds the actual name of the application server. the SQL statements. Syntax: result = G2CurServer() Params: None (parameters will be ignored) Result: The current server name, such as SAMPLE. Null ('') if errors were detected, e.g. when no DB2/2 connection exists. Other: G2SQLCODE contains the DB2/2 SQLCODE ═══ 14.4.3. G2User * Retrieve the connected User ID. ═══ Retrieve the value of the DB2/2 register which holds the user id. used by the application when connecting to the database. Syntax: result = G2User() Params: None (parameters will be ignored) Result: The current (connected) User ID. Null ('') if errors were detected, e.g. when no DB2/2 connection exists. Other: G2SQLCODE contains the DB2/2 SQLCODE ═══ 15. More SQL Set statements ═══ The functions contained herein are to allow (faster) settings of some other modifiable DB2/2 status. Functions to modify Special Register values, if available, can be found under the Special Registers topic. ═══ 15.1. G2SetConnection * Set dormant connection to current ═══ This function changes the state of a DB2/2 database connection from dormant to current. If another connection was available and active (current) when this function was executed, that would be put in a dormant state upon a successful completion of this function, Syntax: result = G2SetConnection(database) /* 1 */ Params: 1. database (required) Name of the database that needs be made current. Result: DB2/2 SQLCODE ═══ 15.2. G2SetCurPkgSet * Set current Packageset ═══ This function sets the collection identifier that will be used to select the package to use for subsequent SQL statements. Syntax: result = G2SetCurPkgSet(collect_id) /* 1 */ Params: 1. collect_id (required) Collection identifier to be set. Result: DB2/2 SQLCODE ═══ 16. Task-specific Functions ═══ Executing two or more SQL statements with one Rexx function call. The functions are geared to peform a dedicated task. ═══ 16.1. G2GetToken * Obtain a surrogate key from a reserved key table ═══ This function returns a surrogate key to be used to uniquely identify an entity. Syntax: result = G2GetToken(SQL-Select-statement, /* 1 */ Maximum-value, /* 2 */ Show-current-value-flag) /* 3 */ Params: 1. SQL-Select-statement (required) All tokens for a standard SQL Select statement Only one column of type SMALLINT or INTEGER may be selected in this SQL statement. Examples: a. "SELECT UNIQUEID FROM QINC.UNIQUEIDS WHERE ID='INVOICE'" b. Assuming MYSQL contains the above SQL statement Use MYSQL (without the quotes since you are passing the value of MYSQL to G2GetToken) c. 'MYSQL' (include quotes since you are telling G2GetToken to use the SQL Select Statement contained in the passed Rexx variable directly) 2. Maximum-value - which value determines the highest possible value for the surrogate-key (optional) If not supplied, this value is set by default to... 32767 if the selected column is of SMALLINT datatype ---OR--- 2147483647 if the column is of INTEGER datatype. 3. Show-current-value-flag - which indicates that the current value must be returned, before it is increased by 1 (optional) If not supplied, the function returns the new value, i.e. after the current value is increased by 1. This is the same value which is updated in the selected table. The flag may be any character, including Rexx null character, as long it is supplied (e.g. G2GetToken('MYSQL',,1) or G2GetToken(mysql,,'x'). Result: A token which could be... - the new surrogate key, if the third parameter is not supplied - the current surrogate key (before increased by 1), if the third parameter is supplied - 0 (zero), if current value is requested and it is NULL Other: G2SQLCODE contains the DB2/2 SQLCODE (Rexx) Syntax error is triggered if the second parameter, Maximum- value, is smaller or equal to the current value, before it is increased by 1. (Rexx) Syntax error is also triggered if the datatype of the one and only selected column is not SMALLINT or INTEGER. If the fourth word in the SQL statement is not the name of the table where the selected column resides, unpredictable results may occur! ═══ 17. HTML-generating functions ═══ These are functions which build HTML-encoded records in a file that is meant for a WWW browser ═══ 17.1. G2H * Write a string to a flat file, with or without HTML (-paired) tag(s). ═══ This function write a string to a file with or without the ASCII record terminating characters (CR LF), and with or without the optionally supplied paired HTML tag. Syntax: result = G2H(HTMLfn, /* 1 */ LF, /* 2 */ Any_text, /* 3 */ Paired_Tag) /* 4 */ Params: 1. HTMLfn - File name to contain the produced HTML (required, holding the produced data) 2. LF - Flag which when supplied with any character indicates that a line feed at the end of the written record is required (optional, instructional) If not supplied, no line feed will be added. 3. Any_text - Any non nullable text (required, functioning as input) 4. Paired_Tag - String, assumed to be HTML Tags, which when supplied, will be embedded with < and >, and at the end of the written string, will be written, embedded with (optional, functioning as input) Result: HTML file name as supplied in the first parameter. Rexx Null character ('') if error occurs during the function exec. Other: Generated HTML codes in the HTMLfn ═══ 17.2. G2HSelect2Clob * Write a Clob to a flat file as HTML textarea element ═══ This function processes an SQL Select statement and create a set of HTML codes to show the selected text Clob on the WWW browser in a textarea object. Syntax: result = G2HSelect2Clob(HTMLfn, /* 1 */ LF, /* 2 */ SQL_select_statement, /* 3 */ Area_rows, /* 4 */ Area_cols, /* 5 */ Area_name, /* 6 */ Extension) /* 7 */ Params: 1. HTMLfn - File name to contain the produced HTML (required, holding the produced data) 2. LF - Flag which when supplied with any character indicates that a line feed at the end of the written record is required (optional, instructional) If not supplied, no line feed will be added. 3. SQL_select_statement (required, functioning as input) All tokens for a standard SQL Select statement, selecting only one table column with datatype = CLOB. Example: SELECT story FROM story_book where... 4. Area_rows - specifies number of lines to be visible when the text area is displayed (optional, instructional) If not supplied it is defaulted to 1. 5. Area_cols - specifies the width of the text area when the text area is displayed (optional, instructional) If not supplied it is defaulted to 40. 6. Area_name (optional, functioning as input) Name of the listbox to be returned to the server. If not supplied it is defaulted to 'TAREA1'. 7. Extension - any parameters for future/specific browsers for the textarea (optional, functioning as input) If not supplied, it is set by default to null. Result: HTML file name as supplied in the first parameter. Rexx Null character ('') if error occurs during the function exec. Other: G2SQLCODE contains the DB2/2-returned SQLCODE Generated HTML textarea codes in the HTMLfn ═══ 17.3. G2HSelect2Img * Write a Blob to a flat file as HTML image element ═══ This function processes an SQL Select statement and create a set of HTML codes to show the selected image on the WWW browser in image object. Syntax: result = G2HSelect2Img(HTMLfn, /* 1 */ LF, /* 2 */ SQL_select_statement, /* 3 */ Imagefn, /* 4 */ Alternate_text, /* 5 */ Alignment, /* 6 */ Extension) /* 7 */ Params: 1. HTMLfn - File name to contain the produced HTML (required, holding the produced data) 2. LF - Flag which when supplied with any character indicates that a line feed at the end of the written record is required (optional, instructional) If not supplied, no line feed will be added. 3. SQL_select_statement (required, functioning as input) All tokens for a standard SQL Select statement, selecting only one table column with datatype = BLOB. Example: SELECT picture FROM emp_photo where... 4. Imagefn - File name to contain the image retrieved from the database (required, holding the retrieved image) 5. Alternate_text - text to show as an alternate to the graphics (optional, functioning as input) If not supplied it is set to Imagefn in upper case. 6. Alignment - position of the graphics compared to the text that follows the graphics (optional, instructional) If not supplied or incorrect, it is set by default to "B"ottom. If this is supplied but Text is not, this is ignored. Possible values are "B"ottom, "M"iddel, and "T"op. 7. Extension - any parameters for future/specific browsers (optional, functioning as input) If not supplied, it is set by default to null. Result: HTML file name as supplied in the first parameter. Rexx Null character ('') if error occurs during the function exec. Other: G2SQLCODE contains the DB2/2-returned SQLCODE Image in the Imagefn Generated HTML IMG codes in the HTMLfn ═══ 17.4. G2HSelect2Input * Get rows and columns of fields and write them to a flat file as HTML input elements ═══ This function processes an SQL Select statement and create a set of HTML codes to show the selected row(s) column(s) on the WWW browser as input field(s). Syntax: result = G2HSelect2Input(HTMLfn, /* 1 */ LF, /* 2 */ SQL_select_statement, /* 3 */ Requested_rows, /* 4 */ Null_char, /* 5 */ Varcharlen, /* 6 */ Style, /* 7 */ Extension4label, /* 8 */ Extension4field) /* 9 */ Params: 1. HTMLfn - File name to contain the produced HTML (required, holding the produced data) 2. LF - Flag which when supplied with any character indicates that a line feed at the end of the written record is required (optional, instructional) If not supplied, no line feed will be added. 3. SQL_select_statement (required, functioning as input) All tokens for a standard SQL Select statement Examples: a. SELECT * FROM STAFF b. SELECT COUNT(*) FROM ORG 4. Requested_rows - number of rows requested to be returned (optional, instructional) If not supplied or if incorrect, it is defaulted to 1 If less rows found than what here is specified, all found rows will still be returned. In any case the number returned in stem.0 should be used to know how many rows are returned. stem.0 value will never exceed the number supplied within this argument. 5. Null_char - character used to represent a 'NULL' or any data of unknown datatype (optional, instructional) If not supplied or if incorrect, it is defaulted to '' If the parameter is longer than one character, only the the first character will be used. The entire column width will be filled by this character. 6. Varcharlen - maximum length of DB2/2 VARCHAR column data (optional, instructional) If not supplied it is defaulted to FORMAT_VARCHAR_MAX. 7. Style - Style of the input fields (optional, instructional) '1' - One column represented by two lines One as title w/ column name, and the other with the input field itself. '2' - One column represented by one line Only the input field is shown. '3' - One column represented by one line Field title followed by the input field. NOTE: If more rows are shown, '
' is placed between blocks of rows. 8. Extension4label - any parameters for future/specific browsers for the label (optional, functioning as input) If not supplied, it is set by default to null. 9. Extension4field - any parameters for future/specific browsers for the input fields (optional, functioning as input) If not supplied, it is set by default to null. Result: HTML file name as supplied in the first parameter. Rexx Null character ('') if error occurs during the function exec. Other: G2SQLCODE contains the DB2/2-returned SQLCODE Generated HTML INPUT codes in the HTMLfn ═══ 17.5. G2HSelect2List * Get rows of data and write them to a flat file as HTML list elements ═══ This function processes an SQL Select statement and create a set of HTML codes to show the selected row(s) column(s) on the WWW browser as list element(s). Syntax: result = G2HSelect2List(HTMLfn, /* 1 */ LF, /* 2 */ SQL_select_statement, /* 3 */ Requested_rows, /* 4 */ Null_char, /* 5 */ Varcharlen, /* 6 */ List_Tag, /* 7 */ Extension4list, /* 8 */ Extension4data) /* 9 */ Params: 1. HTMLfn - File name to contain the produced HTML (required, holding the produced data) 2. LF - Flag which when supplied with any character indicates that a line feed at the end of the written record is required (optional, instructional) If not supplied, no line feed will be added. 3. SQL_select_statement (required, functioning as input) All tokens for a standard SQL Select statement Examples: a. SELECT * FROM STAFF b. SELECT COUNT(*) FROM ORG 4. Requested_rows - number of rows requested to be returned (optional, instructional) If not supplied or if incorrect, it is defaulted to 1 If less rows found than what here is specified, all found rows will still be returned. In any case the number returned in stem.0 should be used to know how many rows are returned. stem.0 value will never exceed the number supplied within this argument. 5. Null_char - character used to represent a 'NULL' or any data of unknown datatype (optional, instructional) If not supplied or if incorrect, it is defaulted to '' If the parameter is longer than one character, only the the first character will be used. The entire column width will be filled by this character. 6. Varcharlen - maximum length of DB2/2 VARCHAR column data (optional, instructional) If not supplied it is defaulted to FORMAT_VARCHAR_MAX. 7. List_Tag - String, with the list descriptive tag (optional, instructional) The tag will be embedded with < and >, and at the end of the list, will be written, embedded with . If not supplied it is defaulted to 'UL'. 8. Extension4list - any parameters for future/specific browsers for the list (optional, functioning as input) If not supplied, it is set by default to null. 9. Extension4data - any parameters for future/specific browsers for the data line(s) (optional, functioning as input) If not supplied, it is set by default to null. Result: HTML file name as supplied in the first parameter. Rexx Null character ('') if error occurs during the function exec. Other: G2SQLCODE contains the DB2/2-returned SQLCODE Generated HTML "list" codes in the HTMLfn ═══ 17.6. G2HSelect2Listbox * Get rows & columns of fields & write them to a flat file as HTML select & option elements ═══ This function processes an SQL Select statement and create a set of HTML codes to show the selected row(s) column(s) on the WWW browser as select and option element(s). Syntax: result = G2HSelect2Listbox(HTMLfn, /* 1 */ LF, /* 2 */ SQL_select_statement, /* 3 */ Requested_rows, /* 4 */ Null_char, /* 5 */ Varcharlen, /* 6 */ Listbox_size, /* 7 */ Select_multiple, /* 8 */ Listbox_name, /* 9 */ Extension4select, /* 10 */ Extension4option) /* 11 */ Params: 1. HTMLfn - File name to contain the produced HTML (required, holding the produced data) 2. LF - Flag which when supplied with any character indicates that a line feed at the end of the written record is required (optional, instructional) If not supplied, no line feed will be added. 3. SQL_select_statement (required, functioning as input) All tokens for a standard SQL Select statement Examples: a. SELECT * FROM STAFF b. SELECT COUNT(*) FROM ORG 4. Requested_rows - number of rows requested to be returned (optional, instructional) If not supplied or if incorrect, it is defaulted to 1 If less rows found than what here is specified, all found rows will still be returned. In any case the number returned in stem.0 should be used to know how many rows are returned. stem.0 value will never exceed the number supplied within this argument. 5. Null_char - character used to represent a 'NULL' or any data of unknown datatype (optional, instructional) If not supplied or if incorrect, it is defaulted to '' If the parameter is longer than one character, only the the first character will be used. The entire column width will be filled by this character. 6. Varcharlen - maximum length of DB2/2 VARCHAR column data (optional, instructional) If not supplied it is defaulted to FORMAT_VARCHAR_MAX. 7. Listbox_size - specifies number of lines to be visible when the listbox is displayed (optional, instructional) If not supplied it is defaulted to 1. 8. Select_multiple - whether or not to allow multiple selection of items from the list (optional, instructional) If not supplied it is defaulted to OFF, i.e. only one item may be selected from the list. 9. Listbox_name (optional, functioning as input) Name of the listbox to be returned to the server. If not supplied it is defaulted to 'LISTBOX1'. 10. Extension4select - any parameters for future/specific browsers for the select (optional, functioning as input) If not supplied, it is set by default to null. 11. Extension4option - any parameters for future/specific browsers for the option fields (optional, functioning as input) If not supplied, it is set by default to null. Result: HTML file name as supplied in the first parameter. Rexx Null character ('') if error occurs during the function exec. Other: G2SQLCODE contains the DB2/2-returned SQLCODE Generated HTML "select and option" codes in the HTMLfn ═══ 17.7. G2HSelect2Table * Get rows and columns of fields and write them to a flat file as HTML table elements ═══ This function processes an SQL Select statement and create a set of HTML codes to show the selected row(s) column(s) on the WWW browser as table(s). Syntax: result = G2HSelect2Table(HTMLfn, /* 1 */ LF, /* 2 */ SQL_select_statement, /* 3 */ Requested_rows, /* 4 */ Null_char, /* 5 */ Varcharlen, /* 6 */ Border_width, /* 7 */ Show_rownum, /* 8 */ Show_header, /* 9 */ Extension4table, /* 10 */ Extension4record, /* 11 */ Extension4header, /* 12 */ Extension4detail) /* 13 */ Params: 1. HTMLfn - File name to contain the produced HTML (required, holding the produced data) 2. LF - Flag which when supplied with any character indicates that a line feed at the end of the written record is required (optional, instructional) If not supplied, no line feed will be added. 3. SQL_select_statement (required, functioning as input) All tokens for a standard SQL Select statement Examples: a. SELECT * FROM STAFF b. SELECT COUNT(*) FROM ORG 4. Requested_rows - number of rows requested to be returned (optional, instructional) If not supplied or if incorrect, it is defaulted to 1 If less rows found than what here is specified, all found rows will still be returned. In any case the number returned in stem.0 should be used to know how many rows are returned. stem.0 value will never exceed the number supplied within this argument. 5. Null_char - character used to represent a 'NULL' or any data of unknown datatype (optional, instructional) If not supplied or if incorrect, it is defaulted to '' If the parameter is longer than one character, only the the first character will be used. The entire column width will be filled by this character. 6. Varcharlen - maximum length of DB2/2 VARCHAR column data (optional, instructional) If not supplied it is defaulted to FORMAT_VARCHAR_MAX. 7. Border_width - whether or not to show the table border and if so, how wide (optional, input) If not supplied it is not to show the border. If supplied it is to show the border with the given width. 8. Show_rownum - whether or not to show the number of each row within the cursor next to each table row (optional, instructional) If not supplied the row number is not shown. If supplied the row number is shown. 9. Show_header - whether or not to show the column header (optional, instructional) If not supplied the header is not shown. If supplied the header is shown as the first row of the table. 10. Extension4table - any parameters for future/specific browsers for the table (optional, functioning as input) If not supplied, it is set by default to null. 11. Extension4record - any parameters for future/specific browsers for the record fields (optional, functioning as input) If not supplied, it is set by default to null. 12. Extension4header - any parameters for future/specific browsers for the header fields (optional, functioning as input) If not supplied, it is set by default to null. 13. Extension4detail - any parameters for future/specific browsers for the detail fields (optional, functioning as input) If not supplied, it is set by default to null. Result: HTML file name as supplied in the first parameter. Rexx Null character ('') if error occurs during the function exec. Other: G2SQLCODE contains the DB2/2-returned SQLCODE Generated HTML TABLE codes in the HTMLfn ═══ 17.8. G2HSelect2Textarea * Get rows & columns of fields & write them to a flat file as an HTML textarea element ═══ This function processes an SQL Select statement and create a set of HTML codes to show the selected row(s) column(s) on the WWW browser as select and option element(s). Syntax: result = G2HSelect2Textarea(HTMLfn, /* 1 */ LF, /* 2 */ SQL_select_statement, /* 3 */ Requested_rows, /* 4 */ Null_char, /* 5 */ Varcharlen, /* 6 */ Area_rows, /* 7 */ Area_cols, /* 8 */ Area_name, /* 9 */ Extension) /* 10 */ Params: 1. HTMLfn - File name to contain the produced HTML (required, holding the produced data) 2. LF - Flag which when supplied with any character indicates that a line feed at the end of the written record is required (optional, instructional) If not supplied, no line feed will be added. 3. SQL_select_statement (required, functioning as input) All tokens for a standard SQL Select statement Examples: a. SELECT * FROM STAFF b. SELECT COUNT(*) FROM ORG 4. Requested_rows - number of rows requested to be returned (optional, instructional) If not supplied or if incorrect, it is defaulted to 1 If less rows found than what here is specified, all found rows will still be returned. In any case the number returned in stem.0 should be used to know how many rows are returned. stem.0 value will never exceed the number supplied within this argument. 5. Null_char - character used to represent a 'NULL' or any data of unknown datatype (optional, instructional) If not supplied or if incorrect, it is defaulted to '' If the parameter is longer than one character, only the the first character will be used. The entire column width will be filled by this character. 6. Varcharlen - maximum length of DB2/2 VARCHAR column data (optional, instructional) If not supplied it is defaulted to FORMAT_VARCHAR_MAX. 7. Area_rows - specifies number of lines to be visible when the text area is displayed (optional, instructional) If not supplied it is defaulted to 1. 8. Area_cols - specifies the width of the text area when the text area is displayed (optional, instructional) If not supplied it is defaulted to 40. 9. Area_name (optional, functioning as input) Name of the listbox to be returned to the server. If not supplied it is defaulted to 'TAREA1'. 10. Extension - any parameters for future/specific browsers for the textarea (optional, functioning as input) If not supplied, it is set by default to null. Result: HTML file name as supplied in the first parameter. Rexx Null character ('') if error occurs during the function exec. Other: G2SQLCODE contains the DB2/2-returned SQLCODE Generated HTML "select and option" codes in the HTMLfn